using System;
using System.Data.SQLite;

namespace StudentManagement
{
    public class School
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public DateTime EstablishDate { get; set; }
    }

    public class Class
    {
        public int Id { get; set; }
        public string ClassName { get; set; }
        public int SchoolId { get; set; }
    }

    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public int ClassId { get; set; }
    }

    public class DBHelper : IDisposable
    {
        private readonly SQLiteConnection _conn;

        public DBHelper()
        {
            _conn = new SQLiteConnection("Data Source=./school.db");
            _conn.Open();
            InitializeDatabase();
        }

        private void InitializeDatabase()
        {
            ExecuteNonQuery(@"
                CREATE TABLE IF NOT EXISTS Schools(
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    EstablishDate DATETIME
                );

                CREATE TABLE IF NOT EXISTS Classes(
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    ClassName TEXT NOT NULL,
                    SchoolId INTEGER REFERENCES Schools(Id)
                );

                CREATE TABLE IF NOT EXISTS Students(
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Age INTEGER,
                    ClassId INTEGER REFERENCES Classes(Id)
                );

                CREATE TABLE IF NOT EXISTS Logs(
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    OperationType TEXT,
                    OperationTime DATETIME DEFAULT CURRENT_TIMESTAMP,
                    Details TEXT
                )");
        }

        public void AddSchool(School school)
        {
            ExecuteNonQuery(
                "INSERT INTO Schools(Name, EstablishDate) VALUES(@name, @date)",
                ("@name", school.Name),
                ("@date", school.EstablishDate.ToString("yyyy-MM-dd")));
            
            LogOperation("AddSchool", $"Added school: {school.Name}");
        }


        private void LogOperation(string operationType, string details)
        {
            ExecuteNonQuery(
                "INSERT INTO Logs(OperationType, Details) VALUES(@type, @details)",
                ("@type", operationType),
                ("@details", details));
        }

        private void ExecuteNonQuery(string sql, params (string, object)[] parameters)
        {
            using var cmd = new SQLiteCommand(sql, _conn);
            foreach (var p in parameters)
            {
                cmd.Parameters.AddWithValue(p.Item1, p.Item2);
            }
            cmd.ExecuteNonQuery();
        }

        public void Dispose() => _conn.Close();
    }
}
